﻿using SqlDao;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Xml.Linq;

namespace clf.code.first
{
    internal class SqliteHandle :BaseHadnle, IHandller
    {

        #region SqliteHandle
        public SqliteHandle(string connstr)
        {
            connectStr = connstr.Trim();
            if (instance == null)
            {
                instance = SqliteHelper.Instance;
            }
            DbHelper.isDebug = true;
            DbHelper.enableErrorLog = true;
            this.databaseName = "main";
        }
        #endregion


        public void StartHandle(Assembly assembly, Action<ProgressResult> callback)
        {
            var thread = new Thread((obj) =>
            {
                string assemblyName = assembly.FullName;
                int progress = 0;
                Notify(callback, progress, "sqlite start handle:" + assemblyName);
                List<Type> classTypes = null;

                try
                {
                    classTypes = assembly.GetTypes().Where((t) =>
                    {
                        return t.GetCustomAttribute<TableAttribute>() != null;
                    }).ToList();
                }
                catch (Exception ex)
                {
                    if (ex is ReflectionTypeLoadException exception)
                    {
                        var ts = exception.Types.ToList();
                        classTypes = ts.Where((t) =>
                        {
                            return t.ToString() != "null" && t.GetCustomAttribute<TableAttribute>() != null;
                        }).ToList();
                    }
                    if (classTypes == null)
                        throw ex;
                }

                if (classTypes == null)
                {
                    Notify(callback, 0, "未能获取到 clf.code.first 注解的实体类");
                    return;
                }

                //记录表中现有的列
                List<string> names = new List<string>();
                var sqliteTables = instance.GetAllTableName(databaseName);
                //mTables = new List<DbSchema>();
                //foreach (var item in sqliteTables)
                //{
                //    mTables.Add(new DbSchema() { TableName = item, TableRows = "0" });
                //}
          
                Notify(callback, 5, "正在进行分类");

                Notify(callback, 7, "分类完成");

                int total = classTypes.Count+ 10;
                StringBuilder createSqlBuilder = new StringBuilder();
                for (int i = 0; i < classTypes.Count; i++)
                {
                    var nt = classTypes[i];
                    Notify(callback, ((progress + i) / total) * 100, "handle :" + nt.Name);

                    var tableAttr = nt.GetCustomAttribute<TableAttribute>();

                    if (tableAttr != null)
                    {
                        string tableName = string.IsNullOrEmpty(tableAttr.Name) ? Helper.CamelCaseToDBnameing(nt.Name) : tableAttr.Name;
                        string   tempTableName = tableName+"_" + "temp";
                        List<string> oldColumns = new List<string>();
                        //0. rename old table
                        if (sqliteTables.Contains(tableName))
                        {
                            createSqlBuilder.AppendLine($"DROP TABLE IF EXISTS main.`{tempTableName}`; ");
                            createSqlBuilder.AppendLine($"ALTER TABLE  \"main\".\"{tableName}\" RENAME TO \"{tempTableName}\";");

                           var schemas =  instance.GetTableSchema<SqliteColumnSchema>(tableName);
                            foreach (var schema in schemas) {
                                oldColumns.Add(schema.Name);
                            }
                        }
                
                        string pk = "PRIMARY KEY (`id`)";
                        //1.add table
                        createSqlBuilder.AppendLine($"-- `{tableName}`  ");
                        createSqlBuilder.AppendLine($"DROP TABLE IF EXISTS main.`{tableName}`; ");
                        createSqlBuilder.AppendLine($"CREATE TABLE `{tableName}` (");
                        List<string> newColumns = new List<string>();
                        //2.add columns
                        var fields = nt.GetFields()?.Where((f) =>
                        {
                            return f.GetCustomAttribute<ColumnsAttribute>() != null || f.GetCustomAttribute<IdAttribute>() != null;
                        }).ToList();

                        SortByName(fields);

                        int ids = 0;

                        foreach (FieldInfo fi in fields)
                        {
                            string res = "";
                            var colAttr = fi.GetCustomAttribute<ColumnsAttribute>();
                            var idAttr = fi.GetCustomAttribute<IdAttribute>();
                            if (colAttr != null || idAttr != null)
                            {
                                string colomnName = "";
                                if (idAttr != null)
                                {
                                    ids++;
                                    if (ids > 1) { throw new Exception("IdAttribute 不能同时使用多个"); }
                                    colomnName = Helper.CamelCaseToDBnameing(fi.Name);
                                    pk = $"PRIMARY KEY (`{colomnName}` ASC)";
                                    newColumns.Add(colomnName);
                                }
                                if (colAttr != null)
                                {
                                    colomnName = string.IsNullOrEmpty(colAttr.Name) ? Helper.CamelCaseToDBnameing(fi.Name) : colAttr.Name;
                                    names.Add(colomnName);
                                    if(!newColumns.Contains(colomnName)) newColumns.Add(colomnName);
                                    res += $"`{colomnName}` ";
                                    //type
                                    res += GetTypeStr(colAttr.SqliteDbType);

                                    //len
                                    res += GetLenthStr(colAttr.SqliteDbType, colAttr.Length, colAttr.Digits);

                                    // null
                                    if (colAttr.IsNull == false)
                                    {
                                        res += $" NOT NULL  ";
                                    }
                                    //default
                                                                   
                                  res += GetDefaultStr(colAttr.SqliteDbType, colAttr.DefaultValue);
                                        
                                    //if (!string.IsNullOrEmpty(colAttr.Comment))
                                    //{
                                    //    res += $" COMMENT '{colAttr.Comment}'";
                                    //}
                                    if (idAttr != null)
                                    {
                                        res += "not null  " + (idAttr.AutoIncrement == true? " AUTOINCREMENT" : "");
                                    }
                                    res += " ,";
                                }
                                else
                                {
                                    res += $"`{colomnName}` ";
                                }
                            }
                            //Append colouns
                            createSqlBuilder.AppendLine(res);
                        }

                        //3.PRIMARY KEY
                        createSqlBuilder.AppendLine(pk);
                        //4.end table
                        createSqlBuilder.AppendLine($");");
                        //5. insert old data to new table
                        if (sqliteTables.Contains(tableName))
                        {
                            List<String> oldfieldList= new List<String>();
                            // 删除 新表没有的 字段
                            foreach (var item in oldColumns) {
                                if (newColumns.Contains(item))
                                {
                                    oldfieldList.Add(item);
                                }                            
                            }
                            string oldfields = "";
                            foreach (var item in oldfieldList)
                            {
                                oldfields += $"\"{item}\",";
                            }
                            oldfields = oldfields.TrimEnd(',');
                            string insertSql = $"INSERT INTO \"main\".\"{tableName}\" ({oldfields}) SELECT {oldfields} FROM \"{tempTableName}\";";
                            createSqlBuilder.AppendLine(insertSql);
                            //6.delete temp table
                            createSqlBuilder.AppendLine($"DROP TABLE IF EXISTS main.`{tempTableName}`; ");
                        }                      
                        createSqlBuilder.AppendLine($"-- end `{tableName}`");
                        createSqlBuilder.AppendLine();
                    }
                }

                createSqlBuilder.AppendLine();

                //saveFile(createSqlBuilder.ToString());

                string sql = RemoveEmptyLines(createSqlBuilder).ToString();

                if (string.IsNullOrEmpty(sql) || sql.Length <= 0)
                {
                    Notify(callback, 100, "sqlite end handle Success: nothing to do .  "); ;
                }
                else
                {
                    int rows = Excute(sql);
                    Notify(callback, 100, "sqlite end handle Success: total " + rows); ;
                }
            });
            thread.Start();
        }

        [Obsolete(message:"不适用的代码，为了保存代码，添加这个方法",error:true)]
        private void handle(Assembly assembly, Action<ProgressResult> callback)
        {
            //CancellationTokenSource cts = new CancellationTokenSource();
            var thread = new Thread((obj) =>
            {
                string assemblyName = assembly.FullName;
                int progress = 0;
                Notify(callback, progress, "sqlite start handle:" + assemblyName);
                List<Type> classTypes = null;

                try
                {
                    classTypes = assembly.GetTypes().Where((t) =>
                    {
                        return t.GetCustomAttribute<TableAttribute>() != null;
                    }).ToList();
                }
                catch (Exception ex)
                {
                    if (ex is ReflectionTypeLoadException exception)
                    {
                        var ts = exception.Types.ToList();
                        classTypes = ts.Where((t) =>
                        {
                            return t.ToString() != "null" && t.GetCustomAttribute<TableAttribute>() != null;
                        }).ToList();
                    }
                    if (classTypes == null)
                        throw ex;
                }

                if (classTypes == null)
                {
                    Notify(callback, 0, "未能获取到 clf.code.first 注解的实体类");
                    return;
                }

                //记录表中现有的列
                List<string> names = new List<string>();
                var sqliteTables = instance.GetAllTableName(databaseName);
                mTables = new List<DbSchema>();
                foreach (var item in sqliteTables)
                {
                    mTables.Add(new DbSchema() { TableName = item, TableRows = "0" });
                }
                //新加入的类
                List<Type> newTypes = new List<Type>();
                List<Type> oldTypes = new List<Type>();
                Notify(callback, 5, "正在进行分类");

                for (int i = 0; i < classTypes.Count; i++)
                {
                    var t = classTypes[i];
                    var exist = IsTypeInTables(t);
                    if (exist)
                    {
                        oldTypes.Add(t);
                    }
                    else
                    {
                        newTypes.Add(t);
                    }
                }
                Notify(callback, 7, "分类完成");

                int total = newTypes.Count + oldTypes.Count + 10;
                StringBuilder createSqlBuilder = new StringBuilder();
                for (int i = 0; i < newTypes.Count; i++)
                {
                    var nt = newTypes[i];
                    Notify(callback, ((progress + i) / total) * 100, "handle :" + nt.Name);

                    var tableAttr = nt.GetCustomAttribute<TableAttribute>();

                    if (tableAttr != null)
                    {
                        string tableName = string.IsNullOrEmpty(tableAttr.Name) ? Helper.CamelCaseToDBnameing(nt.Name) : tableAttr.Name;
                        string pk = "PRIMARY KEY (`id`)";
                        //1.add table
                        createSqlBuilder.AppendLine($" -- `{tableName}`  ");
                        createSqlBuilder.AppendLine($" DROP TABLE IF EXISTS main.`{tableName}`; ");
                        createSqlBuilder.AppendLine($"CREATE TABLE `{tableName}` (");
                        //2.add columns
                        var fields = nt.GetFields()?.Where((f) =>
                        {
                            return f.GetCustomAttribute<ColumnsAttribute>() != null || f.GetCustomAttribute<IdAttribute>() != null;
                        }).ToList();

                        SortByName(fields);

                        int ids = 0;

                        foreach (FieldInfo fi in fields)
                        {
                            string res = "";
                            var colAttr = fi.GetCustomAttribute<ColumnsAttribute>();
                            var idAttr = fi.GetCustomAttribute<IdAttribute>();
                            if (colAttr != null || idAttr != null)
                            {
                                string colomnName = "";
                                if (idAttr != null)
                                {
                                    ids++;
                                    if (ids > 1) { throw new Exception("IdAttribute 不能同时使用多个"); }
                                    colomnName = Helper.CamelCaseToDBnameing(fi.Name);
                                    pk = $"PRIMARY KEY (`{colomnName}` ASC)";
                                }
                                if (colAttr != null)
                                {
                                    colomnName = string.IsNullOrEmpty(colAttr.Name) ? Helper.CamelCaseToDBnameing(fi.Name) : colAttr.Name;
                                    names.Add(colomnName);
                                    res += $"`{colomnName}` ";
                                    //type
                                    res += GetTypeStr(colAttr.SqliteDbType);

                                    //len
                                    res += GetLenthStr(colAttr.SqliteDbType, colAttr.Length, colAttr.Digits);

                                    // null
                                    if (colAttr.IsNull == false)
                                    {
                                        res += $" NOT NULL  ";
                                    }
                                    //default
                                    if (idAttr == null)
                                    {
                                        if (colAttr.SqliteDbType != SqliteDbType.Text)
                                        {
                                            res += GetDefaultStr(SqliteDbType.Text, colAttr.DefaultValue);
                                        }
                                    }
                                    //if (!string.IsNullOrEmpty(colAttr.Comment))
                                    //{
                                    //    res += $" COMMENT '{colAttr.Comment}'";
                                    //}
                                    if (idAttr != null && idAttr.AutoIncrement == true)
                                    {
                                        res += " AUTO_INCREMENT ";
                                    }
                                    res += " ,";
                                }
                                else
                                {
                                    res += $"`{colomnName}` ";
                                }

                            }
                            //Append colouns
                            createSqlBuilder.AppendLine(res);
                        }

                        //3.PRIMARY KEY
                        createSqlBuilder.AppendLine(pk);
                        //4.end table
                        createSqlBuilder.AppendLine($");");
                        createSqlBuilder.AppendLine($"-- end `{tableName}`");
                        createSqlBuilder.AppendLine();
                    }
                }

                createSqlBuilder.AppendLine();
                //old update
                for (int i = 0; i < oldTypes.Count; i++)
                {
                    var ot = oldTypes[i];
                    Notify(callback, ((progress + i) / total) * 100, "handle :" + ot.Name);
                    var tableAttri = ot.GetCustomAttribute<TableAttribute>();
                    if (tableAttri != null)
                    {
                        string tableName = Helper.CamelCaseToDBnameing(ot.Name);
                        if (!string.IsNullOrEmpty(tableAttri.Name))
                        {
                            tableName = tableAttri.Name;
                        }

                        var fields = ot.GetFields().Where((f) =>
                        {
                            //主键 只会第一次构建时创建，一般是（id），后期修改主键无效。
                            // return f.GetCustomAttribute<IdAttribute>() != null ||
                            return f.GetCustomAttribute<ColumnsAttribute>() != null;
                        }).ToList();
                        createSqlBuilder.AppendLine();
                        foreach (var field in fields)
                        {
                            string res = "";
                            var colAttr = field.GetCustomAttribute<ColumnsAttribute>();
                            if (colAttr != null)
                            {
                                string cname = string.IsNullOrEmpty(colAttr.Name) ? Helper.CamelCaseToDBnameing(field.Name) : colAttr.Name;
                                names.Add(cname);
                                string oldtype = GetTypeStr(colAttr.SqliteDbType);
                                res += oldtype;

                                string len = GetLenthStr(colAttr.SqliteDbType, colAttr.Length, colAttr.Digits);
                                res += len;
                                oldtype += len;
                                // null
                                if (colAttr.IsNull == false)
                                {
                                    res += $" NOT NULL  ";
                                }
                                //default
                                if (field.GetCustomAttribute<IdAttribute>() == null)
                                {
                                    res += GetDefaultStr(SqliteDbType.Integer, colAttr.DefaultValue);
                                }

                                //if (!string.IsNullOrEmpty(colAttr.Comment))
                                //{
                                //    res += $" COMMENT '{colAttr.Comment}'";
                                //}
                                var tableColumns = instance.GetTableSchema<SqliteColumnSchema>(tableName);

                                var column = tableColumns.Find((c) => { return c.Name.Equals(cname); });

                                if (column == null)
                                {
                                    // don't exist 
                                    createSqlBuilder.AppendLine($"ALTER TABLE {tableName} ADD COLUMN {cname} {res} ;");
                                }
                                else
                                {
                                    // id 不可以修改
                                    if (oldtype.Trim() != column.Type && cname != "id")
                                    {
                                        //  exist and type diff, modify
                                        createSqlBuilder.AppendLine($"ALTER TABLE {tableName} MODIFY COLUMN {cname} {res} ;");
                                    }
                                }
                                createSqlBuilder.AppendLine();
                            }
                        }
                    }
                }

                //删除多余的列
                createSqlBuilder.AppendLine("--  删除多余的列");
                foreach (var item in oldTypes)
                {
                    Notify(callback, 90, "sqlite end handle:删除" + item.Name + "多余的列");
                    string tableName = Helper.CamelCaseToDBnameing(item.Name);
                    List<SqliteColumnSchema> columnList = instance.GetTableSchema<SqliteColumnSchema>(tableName);

                    FieldInfo[] fieldInfos = item.GetFields();
                    var notCs = columnList.Where((i) => { return !fieldInfos.Any((f) => { return Helper.CamelCaseToDBnameing(f.Name).Equals(i.Name); }); }).ToList();

                    foreach (var notItes in notCs)
                    {
                        createSqlBuilder.AppendLine($" ALTER TABLE {tableName} DROP COLUMN `{notItes.Name}`;");
                    }
                }
                createSqlBuilder.AppendLine("--  end file");

                //saveFile(createSqlBuilder.ToString());

                string sql = RemoveEmptyLines(createSqlBuilder).ToString();

                if (string.IsNullOrEmpty(sql) || sql.Length <= 0)
                {
                    Notify(callback, 100, "sqlite end handle: nothing to do .  "); ;
                }
                else
                {
                    int rows = Excute(sql);
                    Notify(callback, 100, "sqlite end handle: total " + rows); ;
                }
            });
            thread.Start();
        }


        private string GetDefaultStr(SqliteDbType type, object DefaultValue)
        {
            string res = "";
            switch (type)
            {
                case SqliteDbType.Text:
                    res += $" DEFAULT '{DefaultValue}' ";
                    break;
                case SqliteDbType.Real:
                case SqliteDbType.Integer:
                case SqliteDbType.Blob:
                    if (DefaultValue == null)
                    {
                        res = "";
                    }
                    else
                    {
                        res += $" DEFAULT {DefaultValue} ";
                    }
                    break;
                default:
                    if (DefaultValue == null)
                    {
                        res += $" DEFAULT NULL ";
                    }
                    else
                    {
                        res += $" DEFAULT {DefaultValue} ";
                    }
                    break;

            }
            return res;
        }

        private string GetLenthStr(SqliteDbType type, int len, int digits)
        {
            string res = "";
            if (len > 0)
            {
                switch (type)
                {
                    case SqliteDbType.Real:
                        res += $"({len},{digits}) ";
                        break;       
                    case SqliteDbType.Integer:
                    case SqliteDbType.Text:
                    case SqliteDbType.Blob:                 
                        res += $"({len}) ";
                        break;
                    default:

                        break;
                }
            }
            return res;
        }

        private string GetTypeStr(SqliteDbType type)
        {
            string res = "";
            switch (type)
            {
                case SqliteDbType.Integer:
                    res += $" INTEGER";
                    break;
                case SqliteDbType.Text:
                    res += $" TEXT";
                    break;
                case SqliteDbType.Real:            
                    res += $" REAL";
                    break;
                case SqliteDbType.Blob:
                    res += $" BLOB";
                    break;
                default:
                    res += $" TEXT";
                    break;
            }
            return res;
        }
    }
}
